﻿    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Npgsql;
    using NpgsqlTypes;
    using System.Diagnostics;


    namespace OMDR
    {
        class TransactionManager
        {
            private static TransactionManager instance;

            private TransactionManager()
            {
            }

            public static TransactionManager getInstance
            {
                get
                {
                    if (instance == null)
                    {
                        instance = new TransactionManager();
                    }
                    return instance;
                }
            }

            public List<String> getProjectListInDB()
            {
                List<String> locListProject = new List<String>();
                int locCounter;

                NpgsqlConnection locConnection = new NpgsqlConnection("Server=10.226.8.55;Port=5432;User Id=postgres;Password=omdr;Database=postgres;");
                locConnection.Open();
            
                NpgsqlCommand locCommand = new NpgsqlCommand("select nameproject from project", locConnection);

                try
                {
                    NpgsqlDataReader dataReader = locCommand.ExecuteReader();
                    while (dataReader.Read())
                    {
                        for (locCounter = 0; locCounter < dataReader.FieldCount; locCounter++)
                        {
                            locListProject.Add(dataReader[locCounter].ToString());
                        }
                    }
                }

                finally
                {
                    locConnection.Close();
                }

                return locListProject;
            }

            public Project getProjectInDB(String parProjectName)
            {
                String locNameProject = "";
                DateTime locBeginDateProject = DateTime.Today;
                DateTime locEndDateProject = DateTime.Today;
                List<Objective> locObjectiveListProject = new List<Objective>();
                List<DateTime> locMilestoneListProject = new List<DateTime>();

                using (NpgsqlConnection locConnection = new NpgsqlConnection("Server=10.226.8.55;Port=5432;User Id=postgres;Password=omdr;Database=postgres;"))
                {
                    locConnection.Open();

                    using (NpgsqlCommand command = new NpgsqlCommand("select * from project where nameproject = :projectname", locConnection))
                    {
                        int locCounter = 0;
                        
                        command.Parameters.Add(new NpgsqlParameter("projectname", NpgsqlDbType.Varchar));
                        command.Parameters[0].Value = parProjectName;

                        using (NpgsqlDataReader dataReader = command.ExecuteReader())
                        {
                            while (dataReader.Read())
                            {
                                if (locCounter == 0 && locCounter < dataReader.FieldCount)
                                {
                                    locNameProject = dataReader[locCounter].ToString();
                                    locCounter++;
                                }
                                else if (locCounter == 1)
                                {
                                    locBeginDateProject = (DateTime)dataReader[locCounter];
                                    locCounter++;
                                }
                                else
                                {
                                    locEndDateProject = (DateTime)dataReader[locCounter];
                                }
                            }
                        }
                    }

                    using (NpgsqlCommand command = new NpgsqlCommand("select nameobjective, descriptionobjective, typeobjective, phaseobjective, processusobjective from objective where nameproject = :projectname", locConnection))
                    {
                        command.Parameters.Add(new NpgsqlParameter("projectname", NpgsqlDbType.Varchar));
                        command.Parameters[0].Value = parProjectName;

                        using (NpgsqlDataReader dataReader = command.ExecuteReader())
                        {
                            int locCounter = 0;
                            String locNameObjective = "";
                            String locDescriptionObjective = "";
                            String locTypeObjective = "";
                            int locPhaseObjective = 0;
                            String locProcessObjective = "";

                            while (dataReader.Read())
                            {
                                if (locCounter == 0 && locCounter < dataReader.FieldCount)
                                {
                                    locNameObjective = dataReader[locCounter].ToString();
                                    locCounter++;
                                }
                                else if (locCounter == 1 && locCounter < dataReader.FieldCount)
                                {
                                    locDescriptionObjective = dataReader[locCounter].ToString();
                                    locCounter++;
                                }
                                else if (locCounter == 2 && locCounter < dataReader.FieldCount)
                                {
                                    locTypeObjective = dataReader[locCounter].ToString();
                                    locCounter++;
                                }
                                else if (locCounter == 3 && locCounter < dataReader.FieldCount)
                                {
                                    locPhaseObjective = (int)dataReader[locCounter];
                                    locCounter++;
                                }
                                else
                                {
                                    locProcessObjective = dataReader[locCounter].ToString();
                                }
                                Objective locNewObjective = new Objective(locNameObjective, locDescriptionObjective, locTypeObjective, locPhaseObjective, locProcessObjective);
                                locObjectiveListProject.Add(locNewObjective);
                                locCounter = 0;
                            }
                        }
                    }
                    using (NpgsqlCommand command = new NpgsqlCommand("select datemilestone from milestone where nameproject = :projectname", locConnection))
                    {
                        command.Parameters.Add(new NpgsqlParameter("projectname", NpgsqlDbType.Varchar));
                        command.Parameters[0].Value = parProjectName;

                        using (NpgsqlDataReader dataReader = command.ExecuteReader())
                        {
                            int locCounter = 0;
                            DateTime locDateMilestone = DateTime.Today;

                            while (dataReader.Read())
                            {
                                for (locCounter = 0; locCounter < dataReader.FieldCount; locCounter++)
                                {
                                    locDateMilestone = (DateTime)dataReader[locCounter];
                                }
                                locMilestoneListProject.Add(locDateMilestone);
                            }
                        }
                    }                    
                }
                return new Project(locNameProject, locBeginDateProject, locEndDateProject, locMilestoneListProject, locObjectiveListProject);
            }

            public void setObjectiveInDB(Objective parObjective)
            {
                Stopwatch stopw = new Stopwatch();
                stopw.Start();
 
                NpgsqlConnection locConnection = new NpgsqlConnection("Server=10.226.8.55;Port=5432;User Id=postgres;Password=omdr;Database=postgres;");
                locConnection.Open();

                NpgsqlCommand command = new NpgsqlCommand("insert into objective values(:nameobjective, :descriptionobjective, :typeobjective, :phaseobjective, :processusobjective, :nameproject)", locConnection);
                command.Parameters.Add(new NpgsqlParameter("nameobjective", NpgsqlDbType.Varchar));
                command.Parameters.Add(new NpgsqlParameter("descriptionobjective", NpgsqlDbType.Varchar));
                command.Parameters.Add(new NpgsqlParameter("typeobjective", NpgsqlDbType.Varchar));
                command.Parameters.Add(new NpgsqlParameter("phaseobjective", NpgsqlDbType.Integer));
                command.Parameters.Add(new NpgsqlParameter("processusobjective", NpgsqlDbType.Varchar));
                command.Parameters.Add(new NpgsqlParameter("nameproject", NpgsqlDbType.Varchar));
                
                command.Parameters[0].Value = encrypt(parObjective.name);
                command.Parameters[1].Value = encrypt(parObjective.description);
                command.Parameters[2].Value = encrypt(parObjective.type);
                command.Parameters[3].Value = parObjective.phase;
                command.Parameters[4].Value = encrypt(parObjective.process);
                /*command.Parameters[5].Value = DataManager.getInstance.currentProject.name;*/
                command.Parameters[5].Value = DataManager.getInstance.nameProject;

                Int32 rowsaffected;

                try
                {
                    rowsaffected = command.ExecuteNonQuery();
                }

                finally
                {                    
                    locConnection.Close();
                }
                
                stopw.Stop();
                TimeSpan ts = stopw.Elapsed;  

                InformationWindow locInformationWindow = new InformationWindow();
                locInformationWindow.requete.Content = "insert into objective values(:nameobjective, :descriptionobjective, :typeobjective, :phaseobjective, :processusobjective, :nameproject)";
                locInformationWindow.temps.Content = ts.Milliseconds.ToString();
                locInformationWindow.data1.Content = encrypt(parObjective.name.ToString());
                locInformationWindow.data2.Content = decrypt(encrypt(parObjective.name.ToString()));
                locInformationWindow.data3.Content = encrypt(parObjective.description.ToString());
                locInformationWindow.data4.Content = decrypt(encrypt(parObjective.description.ToString()));
                locInformationWindow.data5.Content = encrypt(parObjective.type.ToString());
                locInformationWindow.data6.Content = decrypt(encrypt(parObjective.type.ToString()));
                locInformationWindow.data7.Content = parObjective.phase;
                locInformationWindow.data8.Content = parObjective.phase;
                locInformationWindow.data9.Content = encrypt(parObjective.process.ToString());
                locInformationWindow.data10.Content = decrypt(encrypt(parObjective.process.ToString()));
                locInformationWindow.data11.Content = DataManager.getInstance.nameProject;
                locInformationWindow.data12.Content = DataManager.getInstance.nameProject;
                locInformationWindow.Show();
            }

            private String encrypt(String parData)
            {
                char[] array = parData.ToCharArray();
                for (int i = 0; i < array.Length; i++)
                {
                    int number = (int)array[i];

                    if (number >= 'a' && number <= 'z')
                    {
                        if (number > 'm')
                        {
                            number -= 13;
                        }
                        else
                        {
                            number += 13;
                        }
                    }
                    else if (number >= 'A' && number <= 'Z')
                    {
                        if (number > 'M')
                        {
                            number -= 13;
                        }
                        else
                        {
                            number += 13;
                        }
                    }
                    array[i] = (char)number;
                }
                return new string(array);
            }

            public String decrypt(String parData)
            {
                char[] array = parData.ToCharArray();
                for (int i = 0; i < array.Length; i++)
                {
                    int number = (int)array[i];

                    if (number >= 'a' && number <= 'z')
                    {
                        if (number > 'm')
                        {
                            number -= 13;
                        }
                        else
                        {
                            number += 13;
                        }
                    }
                    else if (number >= 'A' && number <= 'Z')
                    {
                        if (number > 'M')
                        {
                            number -= 13;
                        }
                        else
                        {
                            number += 13;
                        }
                    }
                    array[i] = (char)number;
                }
                return new string(array);
            }

        }
    }
